package net.techwatch.fsindex.dao.jdbc;

import java.io.File;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import net.techwatch.fsindex.FileSystemObject;
import net.techwatch.fsindex.KeyGenerator;
import net.techwatch.fsindex.dao.FileSystemDao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * @author wiv
 * 
 */
public class JdbcFileSystemDao extends JdbcDaoSupport implements FileSystemDao {

	private Log log = LogFactory.getLog(JdbcFileSystemDao.class);

	private final static int BATCH_LIMIT = 300;

	private List<Object[]> creationList;
	private List<Object[]> deletionList;
	private List<Object[]> updateList;

	// Update operations: UPDATE/DELETE/INSERT
	private final static String INSERT_STMT = "insert into FS_ELEMENT (FS_ELEMENT_ID, PARENT_ID, NAME, LAST_UPDATE, IS_ROOT, IS_FILE) values (?, ?, ?, ?, ?, ?)";
	private final static String UPDATE_STMT = "update FS_ELEMENT set LAST_UPDATE=? where FS_ELEMENT_ID=?";
	private final static String DELETE_STMT = "delete from FS_ELEMENT where FS_ELEMENT_ID=?";

	// Select operations
	private final static String SELECT_FILES = "SELECT fe.NAME || '/' || files.NAME path, files.FS_ELEMENT_ID id, files.LAST_UPDATE lastUpdate"
			+ " FROM fs_element fe,"
			+ " (SELECT fe.PARENT_ID PARENT_ID, fe.NAME || '/' || files.NAME NAME, files.FS_ELEMENT_ID FS_ELEMENT_ID, files.LAST_UPDATE LAST_UPDATE"
			+ " FROM fs_element fe,"
			+ " (SELECT fe.PARENT_ID PARENT_ID, fe.NAME || '/' || files.NAME NAME, files.FS_ELEMENT_ID FS_ELEMENT_ID, files.LAST_UPDATE LAST_UPDATE"
			+ " FROM fs_element fe,"
			+ " (SELECT fe.PARENT_ID PARENT_ID,"
			+ " fe.NAME || '/' || files.NAME NAME, files.FS_ELEMENT_ID FS_ELEMENT_ID, files.LAST_UPDATE LAST_UPDATE"
			+ " FROM fs_element fe,"
			+ " (SELECT NAME, PARENT_ID, FS_ELEMENT_ID, LAST_UPDATE"
			+ " FROM fs_element"
			+ " WHERE is_file = 'Y') files"
			+ " WHERE fe.FS_ELEMENT_ID = files.PARENT_ID) files"
			+ " WHERE fe.FS_ELEMENT_ID = files.PARENT_ID) files"
			+ " WHERE fe.FS_ELEMENT_ID = files.PARENT_ID) files"
			+ " WHERE fe.FS_ELEMENT_ID = files.PARENT_ID";
	private final static String SELECT_LEVEL0 = "select fs_element_id id, name path, last_update lastUpdate from fs_element where is_root = 'Y'";
	private final static String SELECT_LEVEL1 = "select fe.fs_element_id id, root.name || '/' || fe.name path, fe.last_update lastUpdate"
			+ " from fs_element fe, (select fs_element_id, name from fs_element where is_root = 'Y') root"
			+ " where fe.parent_id = root.fs_element_id";
	private final static String SELECT_LEVEL2 = "select fe.fs_element_id id, files.name || '/' || fe.name path, fe.last_update lastUpdate"
			+ " from fs_element fe,"
			+ " (select fe.fs_element_id fs_element_id, root.name || '/' || fe.name name"
			+ " from fs_element fe, (select fs_element_id, name from fs_element where is_root = 'Y') root"
			+ " where fe.parent_id = root.fs_element_id) files"
			+ " where fe.parent_id = files.fs_element_id";
	private final static String SELECT_LEVEL3 = "select fe.fs_element_id id, files.name || '/' || fe.name path, fe.last_update lastUpdate"
			+ " from fs_element fe,"
			+ " (select fe.fs_element_id fs_element_id, files.name || '/' || fe.name name"
			+ " from fs_element fe,"
			+ " (select fe.fs_element_id fs_element_id, root.name || '/' || fe.name name"
			+ " from fs_element fe, (select fs_element_id, name from fs_element where is_root = 'Y') root"
			+ " where fe.parent_id = root.fs_element_id) files"
			+ " where fe.parent_id = files.fs_element_id) files"
			+ " where fe.parent_id = files.fs_element_id";
	private final static String SELECT_CHILDREN = "select NAME path from FS_ELEMENT where parent_id=?";

	/**
	 * 
	 */
	private KeyGenerator generator;

	/**
	 * 
	 */
	public JdbcFileSystemDao() {
		creationList = new ArrayList<Object[]>();
		deletionList = new ArrayList<Object[]>();
		updateList = new ArrayList<Object[]>();
	}

	/**
	 * @return
	 */
	public KeyGenerator getGenerator() {
		return this.generator;
	}

	/**
	 * @param generator
	 */
	public void setGenerator(KeyGenerator generator) {
		this.generator = generator;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#create(eu.curia.pex.objectloader.fs.FileSystemObject)
	 */
	public synchronized int create(FileSystemObject fsObj) {
		Object[] params = new Object[6];
		params[0] = generator.getNewId();
		params[1] = fsObj.getParentId();
		params[2] = new File(fsObj.getPath()).getName();
		params[3] = fsObj.getLastUpdate();
		params[4] = "N";
		params[5] = fsObj.isFile() ? "Y": "N";
		fsObj.setId((Long) params[0]);

		// add parameters to the parameters list
		creationList.add(params);

		// check for batch limit
		if (creationList.size() >= BATCH_LIMIT) {
			try {
				QueryRunner runner = new QueryRunner(getDataSource());
				// execute batch
				int result = runner.batch(INSERT_STMT, creationList
						.toArray(new Object[creationList.size()][6])).length;
				creationList.clear();
				return result;
			} catch (SQLException e) {
				log.error("Cannot persist new file object", e);
				return -1;
			}
		}

		return 0;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#update(long, long)
	 */
	public synchronized int update(long id, long lastUpdate) {
		Object[] params = new Object[2];
		params[0] = lastUpdate;
		params[1] = id;

		// add parameters to the parameters list
		updateList.add(params);

		// check for batch limit
		if (updateList.size() >= BATCH_LIMIT) {
			try {
				QueryRunner runner = new QueryRunner(getDataSource());
				// execute batch
				int result = runner.batch(UPDATE_STMT, updateList
						.toArray(new Object[updateList.size()][2])).length;
				updateList.clear();
				return result;
			} catch (SQLException e) {
				log.error("Cannot update file object " + id, e);

				return -1;
			}
		}

		return 0;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#remove(long)
	 */
	public synchronized int remove(long id) {
		Object[] params = new Object[] { id };

		// add parameters to the parameters list
		deletionList.add(params);

		// check for batch limit
		if (deletionList.size() >= BATCH_LIMIT) {
			try {
				QueryRunner runner = new QueryRunner(getDataSource());
				// execute batch
				int result = runner.batch(DELETE_STMT, deletionList
						.toArray(new Object[deletionList.size()][1])).length;
				deletionList.clear();
				return result;
			} catch (SQLException e) {
				log.error("Cannot remove file object " + id, e);

				return -1;
			}
		}

		return 0;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#getFiles()
	 */
	@SuppressWarnings("unchecked")
	public List getFiles() {
		QueryRunner runner = new QueryRunner(getDataSource());

		try {
			return (List) runner.query(SELECT_FILES, new BeanListHandler(
					FileSystemObject.class));
		} catch (SQLException e) {
			log.error("Cannot retrieve files", e);
			return Collections.EMPTY_LIST;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#getDirLevel1()
	 */
	@SuppressWarnings("unchecked")
	public List getDirLevel0() {
		QueryRunner runner = new QueryRunner(getDataSource());

		try {
			return (List) runner.query(SELECT_LEVEL0, new BeanListHandler(
					FileSystemObject.class));
		} catch (SQLException e) {
			log.error("Cannot retrieve level 1 directories", e);
			return Collections.EMPTY_LIST;
		}
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#getDirLevel1()
	 */
	@SuppressWarnings("unchecked")
	public List getDirLevel1() {
		QueryRunner runner = new QueryRunner(getDataSource());

		try {
			return (List) runner.query(SELECT_LEVEL1, new BeanListHandler(
					FileSystemObject.class));
		} catch (SQLException e) {
			log.error("Cannot retrieve level 1 directories", e);
			return Collections.EMPTY_LIST;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#getDirLevel2()
	 */
	@SuppressWarnings("unchecked")
	public List getDirLevel2() {
		QueryRunner runner = new QueryRunner(getDataSource());

		try {
			return (List) runner.query(SELECT_LEVEL2, new BeanListHandler(
					FileSystemObject.class));
		} catch (SQLException e) {
			log.error("Cannot retrieve level 2 directories", e);
			return Collections.EMPTY_LIST;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#getDirLevel3()
	 */
	@SuppressWarnings("unchecked")
	public List getDirLevel3() {
		QueryRunner runner = new QueryRunner(getDataSource());

		try {
			return (List) runner.query(SELECT_LEVEL3, new BeanListHandler(
					FileSystemObject.class));
		} catch (SQLException e) {
			log.error("Cannot retrieve level 3 directories", e);
			return Collections.EMPTY_LIST;
		}
	}

	/* (non-Javadoc)
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#getChildren(long)
	 */
	@SuppressWarnings("unchecked")
	public List getChildren(long parentId) {
		QueryRunner runner = new QueryRunner(getDataSource());

		try {
			return (List) runner.query(SELECT_CHILDREN, parentId, new ColumnListHandler());
		} catch (SQLException e) {
			log.error("Cannot retrieve children of directory " + parentId, e);
			return Collections.EMPTY_LIST;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see eu.curia.pex.objectloader.fs.dao.FileSystemDao#clean()
	 */
	public int clean() {
		int creationResult = 0;
		int deletionResult = 0;
		int updateResult = 0;

		try {
			QueryRunner runner = new QueryRunner(getDataSource());
			// execute creation statement
			if (creationList.size() > 0) {
				creationResult = runner.batch(INSERT_STMT, creationList
						.toArray(new Object[creationList.size()][6])).length;
				creationList.clear();
			}
			// execute deletion statement
			if (deletionList.size() > 0) {
				deletionResult = runner.batch(DELETE_STMT, deletionList
						.toArray(new Object[deletionList.size()][1])).length;
				deletionList.clear();
			}
			// execute creation statement
			if (updateList.size() > 0) {
				updateResult = runner.batch(UPDATE_STMT, updateList
						.toArray(new Object[updateList.size()][2])).length;
				updateList.clear();
			}
		} catch (SQLException e) {
			log.error("Cannot clean properly DAO object", e);
		}

		return creationResult + updateResult + deletionResult;
	}

}
